集計済みのデータをSQL一発で集計前の粒度に戻す方法
アノテーションアプリケーション保守チームの荘野です。3年ぶり2度目の投稿になります。
保守開発ではありものを省コストで改修することが求められるので、一見それって意味あるの？と感じる処理であっても必要に迫られることがあります。
今回のテーマも一から開発するのであれば不要な処理ですが、世のDWH保守担当者の役に立つことを信じてやり方を残します。
何がしたいのか
以下のようなデータを
| date | count |
|---|---|
| 2020-10-15 | 2 |
| 2020-10-16 | 0 |
| 2020-10-17 | 3 |
このように加工するSQLを考えます。
| id | date |
|---|---|
| 1 | 2020-10-15 |
| 2 | 2020-10-15 |
| 1 | 2020-10-17 |
| 2 | 2020-10-17 |
| 3 | 2020-10-17 |
見ての通り、通常の集計処理の逆を行っています。
後者のような最小粒度のテーブルからの集計処理ががっつり作り込まれている環境に、前者のような集計済みデータが追加された！
できるだけ処理に手を入れずに前者のデータを既存集計に追加したい！
というようなシチュエーションを想定しています。
テストデータ準備
先ほどの表と同様のテストデータを準備します。※DBはRedshiftを使用しています。
create table summary(
date DATE,
count BIGINT
);
insert into summary
values
('2020-10-15',2),
('2020-10-16',0),
('2020-10-17',3);
連番テーブル作成
処理に必要になるので、連番テーブルを作成します。
元データのcountの最大値（今回の場合は3）よりも大きい連番である必要があります。今回はlimitに1億を指定して1億件の連番としています。
create table serial_number(
num BIGINT
);
insert into serial_number
select
row_number() over() as num
from
some_moderately_large_table
limit 100000000;
some_moderately_large_tableは1億以上のレコードを持つテーブルであれば何でもいいです。
ただし、あまりにも大きすぎるテーブルを使うと無駄に実行に時間がかかってしまうので、ほどほどのテーブルを選びましょう。
本来はvalueのmax値を取ってきて、それと同じ件数の連番テーブルを作るのが理想ではあります。
ただ、その方法としてプロシージャでループを回し1件1件連番値をInsertする以外の方法が思いつかず、パフォーマンスが出ないため今回は断念しました。
もっと良い方法が思いついたら追記します。
データ投入
本題です。まず目的のデータを突っ込むテーブルはこちらです。
create table minimum_granularity(
id INTEGER,
date DATE
);
最後にINSERTです。
結論はあっさりなのですが、以下で望みのデータが得られます。
insert into minimum_granularity
select
sn.num as id,
sum.date
from
summary sum
inner join
serial_number sn
on sn.num <= sum.count
;
serial_numberテーブルに入っているのは1から始まる連番ですから、sn.num <= sum.countの条件で引っかかるレコード数は当然countと同じになります。
よって結合により狙い通りcountと同じ数のレコードが複製されます。
なお、countが0の場合はレコードなしとしたいので、内部結合としています。
まとめ
分かってしまえば簡単ですが、あまり直感的なやり方ではないので気づきにくいのではないかなと思います。 私は危うくプロシージャを書いてしまうところでした。
誰かの役に立てば幸いです。
